{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "58fd6cd0-74f8-4dc1-b30c-3d921973258b",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "\n",
    "Pandas 提供了易于使用的数据结构和数据分析工具，特别适用于处理结构化数据，如表格型数据（类似于Excel表格）。<br>\n",
    "Pandas 是数据科学和分析领域中常用的工具之一，它使得用户能够轻松地从各种数据源中导入数据，并对数据进行高效的操作和分析。<br>\n",
    "利用pandas可以替代sql对数据库的操作"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e007f5e2-531c-49ac-9783-5a50b926755b",
   "metadata": {},
   "source": [
    "## Series 和 DataFrame： \n",
    "Series：一维数据结构，类似于列表（List），但拥有更强的功能，支持索引。<br>\n",
    "DataFrame类似于一个二维表格，它是 Pandas 中最重要的数据结构。<br>\n",
    "DataFrame 可以看作是由多个 Series 按列排列构成的表格，它既有行索引也有列索引，因此可以方便地进行行列选择、过滤、合并等操作。<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62c29a06-7630-492c-a41a-7fab603fff54",
   "metadata": {},
   "source": [
    "### Series 对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "de76a8cf-c004-403b-a0e9-7942ed5fbf96",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "x    1\n",
      "y    2\n",
      "z    3\n",
      "dtype: int64\n",
      "3\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "a = [1, 2, 3]\n",
    "\n",
    "myvar = pd.Series(a)\n",
    "#print(myvar)\n",
    "#print(myvar[1])\n",
    "myvar = pd.Series(a,index=[\"x\",\"y\",\"z\"])\n",
    "print(myvar)\n",
    "print(myvar[\"z\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66542a3f-196f-4f23-9b74-4da812ff0259",
   "metadata": {},
   "source": [
    "### DataFrame 对象\n",
    "\n",
    "可以理解为一个「带行列标签的二维表格」<br>\n",
    "- 每一 列（column） 通常代表一个特征（字段）<br>\n",
    "- 每一 行（row） 通常代表一条记录（样本）<br>\n",
    "- 索引 每行有唯一的行索引（index）\n",
    "- 可由字典、列表、NumPy 数组、CSV 文件、SQL 等创建\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7938b52-b3f4-46d2-b5b7-01fce2e4cb96",
   "metadata": {},
   "source": [
    "## DataFrame的创建"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef00b441-e8a8-45b7-a923-a0b877af1c47",
   "metadata": {},
   "source": [
    "从字典创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "d6d6cea2-359f-4820-a0f8-f3817e016b5d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Name  Age    City\n",
      "0    Alice   25   Tokyo\n",
      "1      Bob   30   Osaka\n",
      "2  Charlie   35  Nagoya\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = {\n",
    "    #键：值\n",
    "    'Name': ['Alice', 'Bob', 'Charlie'],\n",
    "    'Age': [25, 30, 35],\n",
    "    'City': ['Tokyo', 'Osaka', 'Nagoya']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c5d72190-b09f-49fa-8081-15ad830d19ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Site Age\n",
      "0  Google  10\n",
      "1  Runoob  12\n",
      "2    Wiki  13\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# 创建一个包含网站和年龄的二维ndarray\n",
    "ndarray_data = np.array([\n",
    "    ['Google', 10],\n",
    "    ['Runoob', 12],\n",
    "    ['Wiki', 13]\n",
    "])\n",
    "\n",
    "# 使用DataFrame构造函数创建数据帧\n",
    "df = pd.DataFrame(ndarray_data,columns=['Site', 'Age'])\n",
    "\n",
    "# 打印数据帧\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ddfc7c02-e1c7-4a27-8fa7-f579b7fbd09e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      calories  duration\n",
      "day1       420        50\n",
      "day2       380        40\n",
      "day3       390        45\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = {\n",
    "  \"calories\": [420, 380, 390],\n",
    "  \"duration\": [50, 40, 45]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data, index = [\"day1\", \"day2\", \"day3\"])\n",
    "print(df)\n",
    "# 指定索引\n",
    "print(df.loc[\"day2\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "feb052c2-ceaa-445f-90f9-91156eb6fd13",
   "metadata": {},
   "source": [
    "### loc方法的使用\n",
    "跟numpy中的切片类似\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "422e52f0-bab1-4bef-b6c3-7e35d6989748",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alice</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Charlie</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Name  Age\n",
       "0    Alice   25\n",
       "2  Charlie   35"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "data = {\n",
    "    'Name': ['Alice', 'Bob', 'Charlie'],\n",
    "    'Age': [25, 30, 35],\n",
    "    'City': ['Tokyo', 'Osaka', 'Nagoya']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "df.loc[:, 'Age']          # 所有行的 age 列\n",
    "df.loc[:, ['Name', 'City']] # 所有行的 name 和 city 列\n",
    "df.loc[[0, 2]] #取指定行\n",
    "df.loc[[0,2], ['Name', 'Age']] #切片,闭区间"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "913a29f9-9114-4a28-b4d8-ef3ef60d4cd5",
   "metadata": {},
   "source": [
    "布尔条件筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "4286bce5-ca3c-4f2b-b25a-7816950b9475",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Charlie</td>\n",
       "      <td>Nagoya</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Name    City\n",
       "2  Charlie  Nagoya"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'] > 30, ['Name', 'City']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04fffb36-429a-4d10-97ce-03a59883212f",
   "metadata": {},
   "source": [
    "修改数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "7e957dc6-9acf-46ef-8eb5-7d680af0b406",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Name  Age   City\n",
      "0    Alice   26  Tokyo\n",
      "1      Bob   30  Osaka\n",
      "2  Charlie   35  Tokyo\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "data = {\n",
    "    'Name': ['Alice', 'Bob', 'Charlie'],\n",
    "    'Age': [25, 30, 35],\n",
    "    'City': ['Tokyo', 'Osaka', 'Nagoya']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "df.loc[0, 'Age'] = 26                # 修改单个值\n",
    "df.loc[df['Age'] > 32, 'City'] = 'Tokyo'  # 批量修改\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9239284-12f0-483d-a95b-6b698f1e479f",
   "metadata": {},
   "source": [
    "注意 .loc vs .iloc 区别<br>\n",
    "iloc通过整数位置（index）来取值，而loc通过标签 (label)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7112e7a8-11af-4b58-9d56-ac107a0decd1",
   "metadata": {},
   "source": [
    "#### 练习\n",
    "1. 取出城市为 'Osaka' 的所有信息\n",
    "2. 取出第 'c' 到 'e' 行的 'name' 和 'age'\n",
    "3. 把年龄大于 35 的城市改成 'Fukuoka'\n",
    "4. 提取出名字为 'Alice' 或 'Eve' 的所有列\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f91b6fc-b943-4d6d-80fa-bcb86ab08b76",
   "metadata": {},
   "source": [
    "## pd.read_csv() - 读取 CSV 文件 (非常常用的方法)\n",
    "read_csv() 是从 CSV 文件中读取数据的主要方法，将数据加载为一个 DataFrame。\n",
    "```python\n",
    "import pandas as pd\n",
    "# 读取 CSV 文件，并自定义列名和分隔符\n",
    "df = pd.read_csv('data.csv', sep=';', header=0, names=['A', 'B', 'C'], dtype={'A': int, 'B': float})\n",
    "print(df)\n",
    "```\n",
    "- sep 定义字段分隔符，默认是逗号（,），可以改为其他字符，如制表符（\\t）\n",
    "- header指定行号作为列标题，默认为 0（表示第一行），或者设置为 None 没有标题\n",
    "- names\t自定义列名，传入列名列表\n",
    "- nrows\t读取前 N 行数据\n",
    "- usecols 读取指定的列，可以是列的名称或列的索引\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "519f3daf-68d4-4f2d-b63b-c27bee4899bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Date    CL1    CL2        Roll Coef  day of month Roll last month   \\\n",
      "0     1/2/1985  25,92  25,81   -0,00424382716             1                0   \n",
      "1     1/3/1985  25,84  25,79   -0,00193498452             2                0   \n",
      "2     1/4/1985  25,18  25,19  0,0003971405878             3                0   \n",
      "3     1/7/1985  25,56   25,6   0,001564945227             4                0   \n",
      "4     1/8/1985  25,48  25,51   0,001177394035             5                0   \n",
      "..         ...    ...    ...              ...           ...              ...   \n",
      "261  1/17/1986  23,55   22,6   -0,04033970276            12   -0,05129224652   \n",
      "262  1/20/1986  21,27   21,6    0,01551480959            13   -0,05129224652   \n",
      "263  1/21/1986  20,59  20,81    0,01068479845            14   -0,05129224652   \n",
      "264  1/22/1986  20,39  20,28  -0,005394801373            15   -0,05129224652   \n",
      "265  1/23/1986  19,82  19,88   0,003027245207            16   -0,05129224652   \n",
      "\n",
      "     is end of month        Date  month close        roll coef   ...  \\\n",
      "0                   0   1/31/2000        27,64   -0,03639514731  ...   \n",
      "1                   0   2/29/2000        30,43   -0,03272727273  ...   \n",
      "2                   0   3/31/2000         26,9   -0,06216472073  ...   \n",
      "3                   0   4/28/2000        25,74   -0,04849432929  ...   \n",
      "4                   0   5/31/2000        29,01  -0,007765023633  ...   \n",
      "..                ...         ...          ...              ...  ...   \n",
      "261                 0  10/29/2021        83,57  -0,006641249539  ...   \n",
      "262                 0  11/30/2021        66,18   -0,01361554648  ...   \n",
      "263                 0  12/31/2021        75,21  -0,002969037184  ...   \n",
      "264                 0   1/31/2022        88,15  -0,006203769983  ...   \n",
      "265                 0   2/28/2022        95,72   -0,02053216007  ...   \n",
      "\n",
      "    trading cost   Unnamed: 25 pos aver7 aver9 perf aver7 aver9  \\\n",
      "0               0            0               0                0   \n",
      "1               0            0               0                0   \n",
      "2               0            0               0                0   \n",
      "3               0            0               0                0   \n",
      "4               0            0               0                0   \n",
      "..            ...          ...             ...              ...   \n",
      "261  0,1009601466  1,869053505               1     0,1176773564   \n",
      "262  0,1009601466  1,667605728               1    -0,2014477776   \n",
      "263  0,1038741825  1,514630089               1     0,1500616027   \n",
      "264  0,1068168014  1,686708096               1     0,1750206261   \n",
      "265  0,1068168014  1,778788213               1    0,09208011712   \n",
      "\n",
      "     aver7 aver9 reinvested  aver7 aver9 not reinvested   0              0.1  \\\n",
      "0                          0                           0  0                0   \n",
      "1                          0                           0  0                0   \n",
      "2                          0                           0  0                0   \n",
      "3                          0                           0  0                0   \n",
      "4                          0                           0  0                0   \n",
      "..                       ...                         ... ..              ...   \n",
      "261                        0                 2,299435785  0  0,0003589804954   \n",
      "262                        0                 2,097988008  0  0,0004533091568   \n",
      "263                        0                  2,24804961  0  0,0003988831272   \n",
      "264                        0                 2,423070236  0  0,0003403289847   \n",
      "265                        0                 2,515150354  0  0,0003134141245   \n",
      "\n",
      "     trading cost .1          0.2  \n",
      "0                  0            0  \n",
      "1                  0            0  \n",
      "2                  0            0  \n",
      "3                  0            0  \n",
      "4                  0            0  \n",
      "..               ...          ...  \n",
      "261    0,03542124904  2,264014536  \n",
      "262    0,03542124904  2,062566759  \n",
      "263    0,03542124904  2,212628361  \n",
      "264    0,03542124904  2,387648987  \n",
      "265    0,03542124904  2,479729105  \n",
      "\n",
      "[266 rows x 34 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# 读取 CSV 文件，并自定义列名和分隔符\n",
    "df = pd.read_csv('123.csv',header=0)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be866500-b62c-43e7-9d6f-02376346823b",
   "metadata": {},
   "source": [
    "### head()、tail()和sample()\n",
    "- head( n ) 方法用于读取前面的 n 行，如果不填参数 n ，默认返回 5 行。\n",
    "- tail( n ) 方法用于读取尾部的 n 行，如果不填参数 n ，默认返回 5 行，空行各个字段的值返回 NaN。\n",
    "- sample (n ) 随机选择 n 行数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0ad29a9c-62aa-4b37-8984-abca2db98390",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   1/2/1985  25,92  25,81   -0,00424382716  1  0  0.1  1/31/2000  27,64  \\\n",
      "0  1/3/1985  25,84  25,79   -0,00193498452  2  0    0  2/29/2000  30,43   \n",
      "1  1/4/1985  25,18  25,19  0,0003971405878  3  0    0  3/31/2000   26,9   \n",
      "2  1/7/1985  25,56   25,6   0,001564945227  4  0    0  4/28/2000  25,74   \n",
      "3  1/8/1985  25,48  25,51   0,001177394035  5  0    0  5/31/2000  29,01   \n",
      "4  1/9/1985  25,43  25,43                0  6  0    0  6/30/2000   32,5   \n",
      "\n",
      "    -0,03639514731  ... 0.14 0.15 0.16 0.17  0.18 0.19 0.20 0.21  0.22 0.23  \n",
      "0   -0,03272727273  ...    0    0    0    0     0    0    0    0     0    0  \n",
      "1   -0,06216472073  ...    0    0    0    0     0    0    0    0     0    0  \n",
      "2   -0,04849432929  ...    0    0    0    0     0    0    0    0     0    0  \n",
      "3  -0,007765023633  ...    0    0    0    0     0    0    0    0     0    0  \n",
      "4   -0,05114155251  ...    0    0    0    0     0    0    0    0     0    0  \n",
      "\n",
      "[5 rows x 34 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# 读取 CSV 文件，并自定义列名和分隔符\n",
    "df = pd.read_csv('123.csv',header=1)\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad1c420f-c25e-41a1-a32b-bc60393bde93",
   "metadata": {},
   "source": [
    "## 数据排序\n",
    "\n",
    "在 pandas 中，对数据排序主要用两个方法：sort_values() 和 sort_index()，分别用于按 列的值 或 索引 排序\n",
    "```python\n",
    "df.sort_values(by, axis=0, ascending=True, inplace=False)\n",
    "```\n",
    "```python\n",
    "df.sort_index(axis=0, ascending=True, inplace=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "d3399de4-b2c3-472a-b0f9-61570139acef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      name  age     city\n",
      "0    Alice   25    Tokyo\n",
      "1      Bob   30    Osaka\n",
      "2  Charlie   35   Nagoya\n",
      "3    David   40    Kyoto\n",
      "4      Eve   45  Sapporo\n",
      "      name  age     city\n",
      "4      Eve   45  Sapporo\n",
      "3    David   40    Kyoto\n",
      "2  Charlie   35   Nagoya\n",
      "1      Bob   30    Osaka\n",
      "0    Alice   25    Tokyo\n",
      "      name  age     city\n",
      "3    David   40    Kyoto\n",
      "2  Charlie   35   Nagoya\n",
      "1      Bob   30    Osaka\n",
      "4      Eve   45  Sapporo\n",
      "0    Alice   25    Tokyo\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],\n",
    "    'age': [25, 30, 35, 40, 45],\n",
    "    'city': ['Tokyo', 'Osaka', 'Nagoya', 'Kyoto', 'Sapporo']\n",
    "})\n",
    "\n",
    "# 按 age 升序排序\n",
    "df_sorted = df.sort_values(by='age')\n",
    "print(df_sorted)\n",
    "\n",
    "# 按 age 降序排序\n",
    "df_sorted_desc = df.sort_values(by='age', ascending=False)\n",
    "print(df_sorted_desc)\n",
    "\n",
    "# 按多列排序，先按 city 升序，再按 age 降序\n",
    "df_sorted_multi = df.sort_values(by=['city', 'age'], ascending=[True, False])\n",
    "print(df_sorted_multi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "05e8af1d-f678-4e47-b511-c5c92ff1531e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      name  age     city\n",
      "0    Alice   25    Tokyo\n",
      "1      Bob   30    Osaka\n",
      "2  Charlie   35   Nagoya\n",
      "3    David   40    Kyoto\n",
      "4      Eve   45  Sapporo\n",
      "      name  age     city\n",
      "4      Eve   45  Sapporo\n",
      "3    David   40    Kyoto\n",
      "2  Charlie   35   Nagoya\n",
      "1      Bob   30    Osaka\n",
      "0    Alice   25    Tokyo\n"
     ]
    }
   ],
   "source": [
    "# 按行索引升序排序\n",
    "df_sorted_index = df.sort_index()\n",
    "print(df_sorted_index)\n",
    "\n",
    "# 按行索引降序排序\n",
    "df_sorted_index_desc = df.sort_index(ascending=False)\n",
    "print(df_sorted_index_desc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b42432c0-0bf2-4170-b2c7-c606602e6ef2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
